﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    /// <summary>
    /// Summary description for AbstractDataAccess
    /// </summary>
    public abstract class AbstractDataAccess
    {
        /// <summary>
        /// Gets the connection object.
        /// </summary>
        public SqlConnection ConnectionObject { get; private set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="AbstractDataAccess"/> class.
        /// </summary>
        public AbstractDataAccess()
        {
            ConnectionObject = Connection.GetConnection();
        }

        /// <summary>
        /// Opens the connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public void OpenConnection(SqlConnection connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public void CloseConnection(SqlConnection connection)
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Executes the query as scalar.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public object ExecuteQueryAsScalar(string query, List<SqlParameter> parameters)
        {
            try
            {
                OpenConnection(ConnectionObject);
                var command = new SqlCommand(query, ConnectionObject);
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                return command.ExecuteScalar();
            }
            finally
            {
                CloseConnection(ConnectionObject);
            }
        }

        /// <summary>
        /// Executes the query as SQL data table.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public DataTable ExecuteQueryAsSqlDataTable(string query, List<SqlParameter> parameters)
        {
            try
            {
                OpenConnection(ConnectionObject);
                var command = new SqlCommand(query, ConnectionObject);
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                var adapter = new SqlDataAdapter(command);
                var dataTable = new DataTable("DataTable");
                adapter.Fill(dataTable);

                return dataTable;
            }
            finally
            {
                CloseConnection(ConnectionObject);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string query, List<SqlParameter> parameters)
        {
            try
            {
                OpenConnection(ConnectionObject);
                
                var command = new SqlCommand(query, ConnectionObject);
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                int affectedRow = command.ExecuteNonQuery();
                if (affectedRow > 0)
                {
                    return true;
                }
                return false;
            }
            finally
            {
                CloseConnection(ConnectionObject);
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string query, List<SqlParameter> parameters, SqlTransaction transaction)
        {
            var command = new SqlCommand(query, ConnectionObject, transaction);
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            int affectedRow = command.ExecuteNonQuery();
            if (affectedRow > 0)
            {
                return true;
            }
            return false;
        }
    }
}